 --================================================
-- Drop function template
--================================================
USE SBM
GO

IF OBJECT_ID (N'dbo.FN_NextID') IS NOT NULL
   DROP FUNCTION dbo.FN_NextID
GO

CREATE FUNCTION dbo.FN_NextID (@LastId varchar(10),@Prefix varchar(10),@Size int)
	RETURNS varchar(10)
AS
    BEGIN
        IF(@Lastid = '')
            set @Lastid = @Prefix + REPLICATE (0,@Size - LEN(@Prefix)) 
        declare @Num_NextId int
		declare @Nextid varchar(10)
        set @LastId = LTRIM(RTRIM(@LastId))
        set @Num_NextId = REPLACE(@LastId,LEFT(@LastId, LEN(@Prefix)),'') + 1
        set @Size = @Size - LEN(@Prefix)
        set @Nextid = @Prefix + REPLICATE (0,@Size - LEN(@Prefix)) 
        set @Nextid = @Prefix + RIGHT(REPLICATE(0, @size) + CONVERT (VARCHAR(MAX), @Num_NextId), @Size)
        return @Nextid
    END;
GO

IF OBJECT_ID (N'dbo.FN_DueDate') IS NOT NULL
   DROP FUNCTION dbo.FN_DueDate
GO

CREATE FUNCTION dbo.FN_DueDate (@open datetime, @term int)
	RETURNS datetime
AS
	BEGIN
		return DATEADD(MONTH, @term, @open);
	END;
GO
-- =======================================================
-- Create Stored Procedure Balance
-- =======================================================

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'PROC_Balance' 
)
   DROP PROCEDURE dbo.PROC_Balance
GO


CREATE PROCEDURE  dbo.PROC_Balance @BookID char(10), @Now datetime
AS
	BEGIN
		DECLARE @AccBalance bigint
		DECLARE @Term int
		DECLARE @InterestRate float
		DECLARE @ISDate datetime
		DECLARE @Open datetime
		DECLARE @TypeID char(10)
		SELECT @TypeID = TypeID, @Open = OpeningDate, @ISDate = InterestStartingDate,@AccBalance = AccountBalance
			FROM dbo.SAVINGBOOK WHERE BookID = @BookID
		SELECT @Term=Term,@InterestRate=InterestRate FROM SAVINGTYPE WHERE TypeID=@TypeID
		
		IF(DATEDIFF(DAY, @ISDate, @Now) < 0)
			RETURN 0

		DECLARE @InterestFreePeriod int
		SELECT TOP 1 @InterestFreePeriod = InterestFreePeriod FROM dbo.PARAMETER 
		IF(DATEDIFF(DAY, @ISDate, @Now) < @InterestFreePeriod)
			RETURN @AccBalance

		IF (@Term = -1)
			BEGIN
				DECLARE @Delta int = DATEDIFF(DAY, @ISDate, @Now)
				RETURN (@AccBalance * @InterestRate) * @Delta / 30 + @AccBalance;
			END
		ELSE 
			BEGIN
				DECLARE @DeltaMonth int = DATEDIFF(MONTH, @ISDate, @Now)
				DECLARE @Cycle int = @DeltaMonth / @Term
				DECLARE @NewISDate datetime = DATEADD(DAY, @Cycle * @Term, @ISDate)
				DECLARE @NewAccBalance bigint = @AccBalance * POWER((1 + @InterestRate), @Cycle)
				DECLARE @NewDelta int = DATEDIFF(DAY, @NewISDate, @Now)
				DECLARE @PaymentInterestRate float
				SELECT TOP 1 @PaymentInterestRate = InterestRate FROM SAVINGTYPE
					WHERE Term = -1 ORDER BY TypeID DESC
				RETURN (@NewAccBalance * @PaymentInterestRate) * @NewDelta / 30 + @NewAccBalance;
			END
	END
GO